#region (C) Header Region @ 2012
//====================================================
// 文件: OracleDataAccess.cs
// 描述: 添加了异常处理机制，默认跳转到系统异常中
// 路径: Antu.Core\DataAccess\OracleDataAccess.cs
// ******************版本1 ******************
// 人员: 蔡金保    时间 : 2012/11/15 11:25:39
// 注释:
//====================================================
#endregion

using System;
using System.Data;
using System.Data.OracleClient;
using System.Runtime.InteropServices;

namespace Antu.Core.DataAccess
{
    /// <summary>
    /// Oracle 通用数据访问类（对发生的异常做了处理）
    /// </summary>
    internal class OracleDataAccess : IDataAccess
    {
        private OracleCommand cmd;
        private OracleConnection con;
        private bool isTransaction;
        private bool IsUpdate;
        private OracleDataAdapter sda;
        private string strConnection;
        private OracleTransaction transaction;

        /// <summary>
        /// 构造不带事务的对象
        /// </summary>
        public OracleDataAccess()
        {
            this.IsUpdate = false;
            this.isTransaction = false;
            this.strConnection = "";
            this.isTransaction = false;
        }

        /// <summary>
        /// 使用事务
        /// </summary>
        /// <param name="beginTransaction">true则使用事务</param>
        public OracleDataAccess(bool beginTransaction)
        {
            this.IsUpdate = false;
            this.isTransaction = false;
            this.strConnection = "";
            this.isTransaction = beginTransaction;
        }

        /// <summary>
        /// 不带事务的指定联接串的对象
        /// </summary>
        /// <param name="connectionString">联接串</param>
        public OracleDataAccess(string connectionString)
        {
            this.IsUpdate = false;
            this.isTransaction = false;
            this.strConnection = "";
            this.isTransaction = false;
            this.strConnection = connectionString;
        }

        /// <summary>
        /// 使用事务
        /// </summary>
        /// <param name="beginTransaction">true则使用事务</param>
        /// <param name="connectionString">联接串</param>
        public OracleDataAccess(bool beginTransaction, string connectionString)
        {
            this.IsUpdate = false;
            this.isTransaction = false;
            this.strConnection = "";
            this.isTransaction = beginTransaction;
            this.strConnection = connectionString;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (this.con != null)
            {
                this.con.Close();
                this.Dispose();
            }
        }

        /// <summary>
        /// 提交或撤消更改并关闭数据库连接
        /// </summary>
        /// <param name="commit">True则提交,否则撤消</param>
        public void Close(bool commit)
        {
            if (this.transaction != null)
            {
                if (commit)
                {
                    this.transaction.Commit();
                }
                else
                {
                    this.transaction.Rollback();
                }
            }
            this.Close();
        }

        /// <summary>
        /// 创建一个OracleCommand对象以此来执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <returns>返回OracleCommand对象</returns>
        private OracleCommand CreateCommand(string procName, ref IDataParameter[] prams)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(procName, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = procName;
            }
            this.cmd.CommandType = CommandType.StoredProcedure;
            if ((this.transaction != null) && this.isTransaction)
            {
                this.cmd.Transaction = this.transaction;
            }
            if (prams != null)
            {
                foreach (IDataParameter parameter in prams)
                {
                    this.cmd.Parameters.Add(parameter);
                }
            }
            return this.cmd;
        }

        /// <summary>
        /// 检查对应于条件的数据是否存在
        /// </summary>
        /// <param name="tableName">表名</param> 
        /// <param name="where">条件,包括Where语句,如Where a=2 </param>
        /// <returns></returns>
        public bool DataExists(string tableName, string where)
        {
            OracleDataReader dr;
            string SqlStr = "SELECT Rownum FROM " + tableName + " " + where;
            this.RunSQL(SqlStr, out dr);
            if (dr != null)
            {
                if (dr.HasRows)
                {
                    dr.Close();
                    dr.Dispose();
                    if (!this.isTransaction)
                    {
                        this.Close();
                    }
                    return true;
                }
            }
            dr.Close();
            dr.Dispose();
            if (!this.isTransaction)
            {
                this.Close();
            }
            return false;
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (this.con != null)
            {
                this.con.Dispose();
                this.con = null;
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用，为空访问null,尽可能使用存储过程.返回IDataReader
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>IDataReader</returns>
        public IDataReader GetDataReader(string sql)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(sql, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = sql;
            }
            this.cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                this.cmd.Transaction = this.transaction;
            }
            IDataReader ireader = null;
            try
            {
                ireader = this.cmd.ExecuteReader(CommandBehavior.SingleRow);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            return ireader;
        }

        /// <summary>
        /// 运行SQL语句返回第一行第一个字段的值（字符串形式）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetValue(string sql)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(sql, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = sql;
            }
            this.cmd.CommandType = CommandType.Text;
            object obj = null;
            try
            {
                obj = this.cmd.ExecuteScalar();
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }

            string value = "";
            if (obj != null)
            {
                value = obj.ToString();
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
            return value;
        }

        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        private void Open()
        {
            if (this.con == null)
            {
                if (this.strConnection == string.Empty)
                {
                    this.con = new OracleConnection(ConnectionText.OracleText);
                }
                else
                {
                    this.con = new OracleConnection(this.strConnection);
                }
            }
            if (this.con.State == ConnectionState.Closed)
            {
                if (this.strConnection == string.Empty)
                {
                    this.con.ConnectionString = ConnectionText.OracleText;
                }
                else
                {
                    this.con.ConnectionString = this.strConnection;
                }
                this.con.Open();
                if (this.isTransaction)
                {
                    this.transaction = this.con.BeginTransaction();
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <returns>返回存储过程返回值</returns>
        public int RunProc(string procName)
        {
            IDataParameter[] prams = null;
            this.cmd = this.CreateCommand(procName, ref prams);
            object obj = 0;
            try
            {
                obj = this.cmd.ExecuteScalar();
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <returns>返回存储过程返回值</returns>
        public int RunProc(string procName, ref IDataParameter[] prams)
        {
            this.cmd = this.CreateCommand(procName, ref prams);
            object obj = 0;
            try
            {
                obj = this.cmd.ExecuteScalar();
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 执行存储过程,将结果以DataSet返回
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">参数数组</param>
        /// <param name="ds">返回的DataSet</param>
        public void RunProc(string procName, ref IDataParameter[] prams, out DataSet ds)
        {
            this.cmd = this.CreateCommand(procName, ref prams);
            this.sda = new OracleDataAdapter(this.cmd);
            ds = new DataSet();

            try
            {
                this.sda.Fill(ds);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }

            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行存储过程,将结果以DataTable返回
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">参数数组</param>
        /// <param name="dt">返回结果集的DataTable</param>
        public void RunProc(string procName, ref IDataParameter[] prams, out DataTable dt)
        {
            this.cmd = this.CreateCommand(procName, ref prams);
            this.sda = new OracleDataAdapter(this.cmd);
            dt = new DataTable();

            try
            {
                this.sda.Fill(dt);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行存储过程,以DataReader返回
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="dataReader">返回存储过程返回值</param>
        /// <param name="prams">参数集</param>
        public void RunProc(string procName, ref IDataParameter[] prams, out OracleDataReader dataReader)
        {
            this.cmd = this.CreateCommand(procName, ref prams);
            dataReader = null;
            try
            {
                dataReader = this.cmd.ExecuteReader(CommandBehavior.CloseConnection);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程
        /// </summary>
        /// <param name="sql"></param>		
        /// <returns>返回执行语句受影响的记录的行数</returns>
        public int RunSql(string sql)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(sql, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = sql;
            }
            this.cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                this.cmd.Transaction = this.transaction;
            }
            object obj = 0;
            try
            {
                obj = this.cmd.ExecuteNonQuery();
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数dt返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dt">结果集</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataTable dt)
        {
            this.RunSql(sql, out dt, false);
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="prams">所需参数</param>
        /// <returns>返回值</returns>
        public DataTable RunSql(string sql, ref IDataParameter[] prams)
        {
            this.cmd = this.CreateCommand(sql, ref prams);
            this.cmd.CommandType = CommandType.Text;
            this.sda = new OracleDataAdapter(this.cmd);
            DataTable dt = new DataTable();
            try
            {
                this.sda.Fill(dt);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数ds返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ds">结果集</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataSet ds)
        {
            this.RunSql(sql, out ds, false);
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数ds返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ds">结果集</param>
        /// <param name="isUpdate">返回的数据集是否支持更新，如果是，则可以调用此类的Update方法进行更新数据集</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataSet ds, bool isUpdate)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(sql, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = sql;
            }
            this.cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                this.cmd.Transaction = this.transaction;
            }
            this.sda = new OracleDataAdapter(this.cmd);
            if (isUpdate)
            {
                OracleCommandBuilder cBuilder = new OracleCommandBuilder(this.sda);
                this.IsUpdate = isUpdate;
            }
            ds = new DataSet();
            try
            {
                this.sda.Fill(ds);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }

            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数dt返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dt">结果集</param>
        /// <param name="isUpdate">返回的数据集是否支持更新，如果是，则可以调用此类的Update方法进行更新数据集</param> 
        /// <returns></returns>
        public void RunSql(string sql, out DataTable dt, bool isUpdate)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(sql, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = sql;
            }
            this.cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                this.cmd.Transaction = this.transaction;
            }
            this.sda = new OracleDataAdapter(this.cmd);
            OracleCommandBuilder cBuilder = null;
            if (isUpdate)
            {
                cBuilder = new OracleCommandBuilder(this.sda);
                this.IsUpdate = isUpdate;
            }
            dt = new DataTable();
            try
            {
                this.sda.Fill(dt);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,为空访问null,尽可能使用存储过程.以参数SqlDataReader返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dataReader"></param>
        private void RunSQL(string sql, out OracleDataReader dataReader)
        {
            this.Open();
            if (!((this.cmd != null) && this.isTransaction))
            {
                this.cmd = new OracleCommand(sql, this.con);
            }
            else
            {
                this.cmd.Parameters.Clear();
                this.cmd.CommandText = sql;
            }
            this.cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                this.cmd.Transaction = this.transaction;
            }
            dataReader = null;
            try
            {
                dataReader = this.cmd.ExecuteReader(CommandBehavior.CloseConnection);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
        }

        /// <summary>
        /// 更新指定的DataSet中的指定表的数据到源数据库中
        /// </summary>
        /// <param name="dt">指定要更新的数据表</param>
        public void Update(DataTable dt)
        {
            if (!this.IsUpdate)
            {
                throw new Exception("Update方法必须要先执行了RunSQL方法并设了参数isUpdate＝true才有效");
            }
            try
            {
                this.sda.Update(dt);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
        }

        /// <summary>
        /// 更新指定的DataSet中的指定表的数据到源数据库中
        /// </summary>
        /// <param name="ds">指定要更新的数据集</param>
        /// <param name="TableName">指定是数据集中的那个表</param>
        public void Update(DataSet ds, string TableName)
        {
            if (!this.IsUpdate)
            {
                throw new Exception("Update方法必须要先执行了RunSQL方法并设了参数isUpdate＝true才有效");
            }
            try
            {
                this.sda.Update(ds, TableName);
                Writedata(this.cmd);
            }
            catch (Exception exc)
            {
                Catchdataexc(exc, this.cmd);
            }
        }

        /// <summary>
        /// 联接串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return this.strConnection;
            }
            set
            {
                if (this.con != null)
                {
                    this.con.Close();
                }
                this.strConnection = value;
            }
        }

        /// <summary>
        /// 记录sql语句
        /// </summary>
        /// <param name="cmd"></param>
        private void Writedata(OracleCommand cmd)
        {
            string datainfo = "0";//默认不记录信息日志
            if (System.Configuration.ConfigurationManager.AppSettings["datainfo"] != null)
                datainfo = System.Configuration.ConfigurationManager.AppSettings["datainfo"].ToString();
            string debuger = Antu.Utilities.UtilitiesPage.GetStringPar("debuger", "0");
            if (datainfo == "1" || debuger == "1")
                Antu.Core.Logger.LogManager.Manager.OutputInfoLog(cmd.CommandText, new object[] { cmd.Parameters });
        }
        /// <summary>
        /// 获取异常数据并记录0过滤1抛给系统异常
        /// </summary>
        /// <param name="exc"></param>
        private void Catchdataexc(Exception exc, OracleCommand cmd)
        {
            string dataexc = "1";//默认抛给系统异常
            if (System.Configuration.ConfigurationManager.AppSettings["dataexc"] != null)
                dataexc = System.Configuration.ConfigurationManager.AppSettings["dataexc"].ToString();
            if (dataexc == "0")//过滤
                Antu.Core.Logger.LogManager.Manager.OutputErrorLog(exc, new object[] { cmd.CommandText });
            else
                throw exc;
        }
    }
}

